﻿using FreeADO.Common;
using FreeADO.DBUtility;
using Models.Common;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Web.Filters;


namespace Web.Controllers.MVC
{
    [MvcAuthentication]
    public class HomeController : Controller
    {
        [AllowAnonymous]
        // GET: Home
        public ActionResult Index(int ID)
        {
            string VipCardCode = Convert.ToString(DbHelperSQL.GetSingle("select VipCardCode  from nVipCardSign where VipCardID=" + ID + ""));
            string Bname = Convert.ToString(DbHelperSQL.GetSingle("select Bname from nVipCardSign where VipCardID=" + ID + ""));
            ViewBag.In = VipCardCode;
            ViewBag.In2 = Bname;
            return View();
        }
        /// <summary>
        /// 采购单据明细表
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        public ActionResult DlyBuy()
        {
            return View();
        }
        [AllowAnonymous]
        public ActionResult SaleDetail(int? SaleID)
        {
            return View();
        }
        /// <summary>
        /// 进销存单据索引
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        public ActionResult Dlyndx()
        {
            return View();
        }
        /// <summary>
        /// 销售单据明细表
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        public ActionResult DlySale()
        {
            return View();
        }
        /// <summary>
        /// 费用支出明细表
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        public ActionResult DlyA()
        {
            return View();
        }
        [AllowAnonymous]
        public ActionResult Add(int ID)
        {
            ViewBag.ID = ID;
            ViewBag.abc = "";
            if (ID > 0)
            {
                DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Test where ID=" + ID).Tables[0];
                ViewBag.Name = "";
                ViewBag.Phone = "";
                ViewBag.Remarks = "";
                foreach (DataRow item in dt.Rows)
                {
                    ViewBag.Name = Convert.ToString(item["Name"]);
                    ViewBag.Phone = Convert.ToString(item["Phone"]);
                    ViewBag.Remarks = Convert.ToString(item["Remarks"]);
                }
            }
            return View();
        }
        [AllowAnonymous]
        public ActionResult edit(int ID)
        {
            ViewBag.ID = ID;
            ViewBag.abc = "";
            if (ID > 0)
            {
                DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Test where ID=" + ID).Tables[0];
                ViewBag.Name = "";
                ViewBag.Phone = "";
                ViewBag.Remarks = "";
                foreach (DataRow item in dt.Rows)
                {
                    ViewBag.Name = Convert.ToString(item["Name"]);
                    ViewBag.Phone = Convert.ToString(item["Phone"]);
                    ViewBag.Remarks = Convert.ToString(item["Remarks"]);
                }
            }
            return View();
        }

        /// <summary>
        /// 采购单据明细表查询
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="Search"></param>
        /// <returns></returns>
        [AllowAnonymous]
        public string DlyBuyList(int PageIndex = 1, int PageSize = 5, string Search = "")
        {
            GridResult result = new GridResult();
            //SqlParameter[] parameters =
            //{
            //                                new SqlParameter("@PageIndex",SqlDbType.Int),
            //                                new SqlParameter("@PageSize",SqlDbType.Int),
            //                                new SqlParameter("@Search",SqlDbType.NVarChar,40000)};
            //parameters[0].Value = PageIndex;
            //parameters[1].Value = PageSize;
            //parameters[2].Value = Search;
            //DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_VIPList", parameters, "aaa", 3000);
            if (Search == "")
            {
                DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query("select  DlyBuy.Vchcode,atypecw.FullName as atypecwName,Btype.FullName as BtypeName,Employee.FullName as EmployeeName,Stock.FullName as StockName,ptype.FullName as ptypeName,DlyBuy.Qty,DlyBuy.discount,DlyBuy.DiscountPrice,DlyBuy.costtotal,DlyBuy.costprice,DlyBuy.Blockno, DlyBuy.price, DlyBuy.total, DlyBuy.Prodate, TaxPrice,TaxTotal, DlyBuy.comment, DlyBuy.date, DlyBuy.usedtype, DlyBuy.period, DlyBuy.tax_total,DlyBuy.tax, DlyBuy.discounttotal,T_GBL_Vchtype.fullname as VchtypeName, redword, DlyBuy.dlyorder,DlyBuy.unit, DlyBuy.PDETAIL, DlyBuy.SourceDlyOrder, DlyBuy.Toqty, DlyBuy.RedOld, DlyBuy.UserDefined01, DlyBuy.UserDefined02, DlyBuy.WLDZ, DlyBuy.QtyOther, DlyBuy.FreeDom01, DlyBuy.FreeDom02, DlyBuy.FreeDom03, DlyBuy.FreeDom04, DlyBuy.FreeDom05, DlyBuy.FreeDom06,DlyBuy.FreeDom07, DlyBuy.FreeDom08, DlyBuy.FreeDom09, DlyBuy.FreeDom10, DlyBuy.ToOutQty,DlyBuy.InNumber, DlyBuy.InVchCode, DlyBuy.InVchType, DlyBuy.InDlyorder, DlyBuy.SourceVchcode, DlyBuy.SourceVchtype, DlyBuy.RowNo, Department.FullName as DeparmentName, DlyBuy.Appoint,DlyBuy.Draft, DlyBuy.FeeTotal, DlyBuy.CalcCostOrder, DlyBuy.FreeDom11, DlyBuy.FreeDom12, DlyBuy.FreeDom13, DlyBuy.FreeDom14, DlyBuy.FreeDom15, DlyBuy.FreeDom16, DlyBuy.FreeDomDateDif, DlyBuy.Custom3, DlyBuy.Custom4, DlyBuy.SourceVchtype2, DlyBuy.SourceVchcode2, DlyBuy.SourceDlyOrder2, SettleBtype.FullName as SettleBtypeName, DlyBuy.BtypeOtherCode, DlyBuy.DetailSign, DlyBuy.ProduceDate, DlyBuy.IniInStockToQty, DlyBuy.IniInStockBackQty, DlyBuy.InStockToQty, DlyBuy.InStockBackQty, InstockBackSign, DlyBuy.IniInstockToQtyOther, Mtype.FullName as MtypeName, DlyBuy.IniToFactQty, DlyBuy.ParVchtype from DlyBuy LEFT   join atypecw on DlyBuy.atypeid=atypecw.typeId LEFT   join Btype on DlyBuy.btypeid=Btype.typeId LEFT   join Employee on DlyBuy.etypeid=Employee.typeId LEFT   join Stock on DlyBuy.ktypeid=Stock.typeId LEFT   join ptype on DlyBuy.PtypeId=ptype.typeId LEFT   join T_GBL_Vchtype on DlyBuy.Vchtype=T_GBL_Vchtype.vchtype LEFT   join Department on DlyBuy.ProjectID=Department.typeid LEFT   join Mtype on DlyBuy.mtypeid=Mtype.Typeid LEFT   join Btype as SettleBtype on DlyBuy.SettleBtypeId=SettleBtype.typeId where Draft!=1").Tables[0];
                result.count = 0;
                result.data = dt1;
                result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
                return Newtonsoft.Json.JsonConvert.SerializeObject(result);
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select  DlyBuy.Vchcode,atypecw.FullName as atypecwName,Btype.FullName as BtypeName,Employee.FullName as EmployeeName,Stock.FullName as StockName,ptype.FullName as ptypeName,DlyBuy.Qty,DlyBuy.discount,DlyBuy.DiscountPrice,DlyBuy.costtotal,DlyBuy.costprice,DlyBuy.Blockno, DlyBuy.price, DlyBuy.total, DlyBuy.Prodate, TaxPrice,TaxTotal, DlyBuy.comment, DlyBuy.date, DlyBuy.usedtype, DlyBuy.period, DlyBuy.tax_total,DlyBuy.tax, DlyBuy.discounttotal,T_GBL_Vchtype.fullname as VchtypeName, redword, DlyBuy.dlyorder,DlyBuy.unit, DlyBuy.PDETAIL, DlyBuy.SourceDlyOrder, DlyBuy.Toqty, DlyBuy.RedOld, DlyBuy.UserDefined01, DlyBuy.UserDefined02, DlyBuy.WLDZ, DlyBuy.QtyOther, DlyBuy.FreeDom01, DlyBuy.FreeDom02, DlyBuy.FreeDom03, DlyBuy.FreeDom04, DlyBuy.FreeDom05, DlyBuy.FreeDom06,DlyBuy.FreeDom07, DlyBuy.FreeDom08, DlyBuy.FreeDom09, DlyBuy.FreeDom10, DlyBuy.ToOutQty,DlyBuy.InNumber, DlyBuy.InVchCode, DlyBuy.InVchType, DlyBuy.InDlyorder, DlyBuy.SourceVchcode, DlyBuy.SourceVchtype, DlyBuy.RowNo, Department.FullName as DeparmentName, DlyBuy.Appoint,DlyBuy.Draft, DlyBuy.FeeTotal, DlyBuy.CalcCostOrder, DlyBuy.FreeDom11, DlyBuy.FreeDom12, DlyBuy.FreeDom13, DlyBuy.FreeDom14, DlyBuy.FreeDom15, DlyBuy.FreeDom16, DlyBuy.FreeDomDateDif, DlyBuy.Custom3, DlyBuy.Custom4, DlyBuy.SourceVchtype2, DlyBuy.SourceVchcode2, DlyBuy.SourceDlyOrder2, SettleBtype.FullName as SettleBtypeName, DlyBuy.BtypeOtherCode, DlyBuy.DetailSign, DlyBuy.ProduceDate, DlyBuy.IniInStockToQty, DlyBuy.IniInStockBackQty, DlyBuy.InStockToQty, DlyBuy.InStockBackQty, InstockBackSign, DlyBuy.IniInstockToQtyOther, Mtype.FullName as MtypeName, DlyBuy.IniToFactQty, DlyBuy.ParVchtype from DlyBuy LEFT   join atypecw on DlyBuy.atypeid=atypecw.typeId LEFT   join Btype on DlyBuy.btypeid=Btype.typeId LEFT   join Employee on DlyBuy.etypeid=Employee.typeId LEFT   join Stock on DlyBuy.ktypeid=Stock.typeId LEFT   join ptype on DlyBuy.PtypeId=ptype.typeId LEFT   join T_GBL_Vchtype on DlyBuy.Vchtype=T_GBL_Vchtype.vchtype LEFT   join Department on DlyBuy.ProjectID=Department.typeid LEFT   join Mtype on DlyBuy.mtypeid=Mtype.Typeid LEFT   join Btype as SettleBtype on DlyBuy.SettleBtypeId=SettleBtype.typeId where Draft!=1 and Btype.FullName ='" + Search + "'").Tables[0];
            result.count = 0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        //[AllowAnonymous]
        //public string ArryListAsync(int PageIndex = 1, int PageSize = 20, string Search = "")
        //{
        //    GridResult result = new GridResult();
        //    SqlParameter[] parameters = {
        //                                    new SqlParameter("@PageIndex",SqlDbType.Int),
        //                                    new SqlParameter("@PageSize",SqlDbType.Int),
        //                                    new SqlParameter("@Search",SqlDbType.NVarChar,40000)};
        //    parameters[0].Value = PageIndex;
        //    parameters[1].Value = PageSize;
        //    parameters[2].Value = Search;
        //    DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_TestList", parameters, "aaa", 3000);
        //    result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
        //    result.data = ds.Tables[1];
        //    if(result.count > 0)//大于0时反序列化,如果数据为空反序列化表一则返回null出现错误
        //    {
        //        result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>> (Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[1]))[0];

        //    } 
        //    return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        //}

        //[AllowAnonymous]
        //public ActionResult AddUsersAsync(DlyBuy t)//添加操作员
        //{
        //    var cookies = Web.Common.CookiesManagement.GetCookies();
        //    var ticket = Web.Common.CookiesManagement.GetTicket(cookies);
        //    GridResult result = new GridResult();
        //    if (t.ID == 0)
        //    {
        //        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("INSERT INTO Test(Name,Phone,Remarks)" + "VALUES('" + t.Name + "','" + t.Phone + "','" + t.Remarks + "')");
        //    }
        //    else
        //    { 
        //            FreeADO.DBUtility.DbHelperSQL.ExecuteSql("update Test set Name='" + t.Name + "',Phone='" + t.Phone + "',Remarks='" + t.Remarks + "' where ID=" + t.ID);
        //        }

        //    return Json(result);
        //}
        /// <summary>
        /// 销售单据明细表查询
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="Search"></param>
        /// <returns></returns>
        [AllowAnonymous]
        public string DlySaleList(int PageIndex = 1, int PageSize = 5, string Search = "")
        {
            GridResult result = new GridResult();
            if (Search == "")
            {
                DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query("select  DlySale.Vchcode,Btype.FullName as BtypeName,Employee.FullName as EmployeeName,Stock.FullName as StockName,ptype.FullName as ptypeName,DlySale.Qty,DlySale.discount,DlySale.DiscountPrice,DlySale.costtotal,DlySale.costprice,DlySale.Blockno,DlySale.price,DlySale.total,DlySale.Prodate,DlySale.TaxPrice,DlySale.TaxTotal,DlySale.comment,DlySale.date,DlySale.usedtype,DlySale.period,DlySale.tax_total,DlySale.tax,DlySale.discounttotal,T_GBL_Vchtype.fullname as T_GBL_VchtypeName,DlySale.redword,DlySale.dlyorder,DlySale.wldzgroup,DlySale.unit,DlySale.PDETAIL,DlySale.groupover,DlySale.SourceDlyOrder,DlySale.planprice,DlySale.plantotal,DlySale.Toqty,DlySale.RedOld,DlySale.recalc,workshop.FullName as workshopName, custom1.FullName as custom1Name, custom2.FullName as custom2Name, DlySale.FreeDom01, DlySale.FreeDom02, DlySale.FreeDom03, DlySale.FreeDom04, DlySale.FreeDom05, DlySale.WLDZ, DlySale.FreeDom06, DlySale.FreeDom07, DlySale.FreeDom08, DlySale.FreeDom09, DlySale.FreeDom10, DlySale.QtyOther, DlySale.ToOutQty, DlySale.InNumber, DlySale.InVchCode, DlySale.InVchType, DlySale.InDlyorder, DlySale.SourceVchcode, DlySale.SourceVchtype, DlySale.RowNo, Department.FullName as DepartmentName, DlySale.Appoint, DlySale.Draft, DlySale.CalcCostOrder, DlySale.FreeDom11, DlySale.FreeDom12, DlySale.FreeDom13, DlySale.FreeDom14, DlySale.FreeDom15, DlySale.FreeDom16, DlySale.FreeDomDateDif, DlySale.Custom3, DlySale.Custom4, Btype.FullName as BtypeName, DlySale.BtypeOtherCode, DlySale.DetailSign, DlySale.ProduceDate, DlySale.PriceTraceSign, Mtype.FullName as MtypeName from DlySale LEFT join Btype on DlySale.btypeid = Btype.typeId LEFT join Employee on DlySale.btypeid = Employee.typeId LEFT join Stock on DlySale.btypeid = Stock.typeId LEFT join ptype on DlySale.btypeid = ptype.typeId LEFT join T_GBL_Vchtype on DlySale.btypeid = T_GBL_Vchtype.typeId LEFT join workshop on DlySale.btypeid = workshop.typeId LEFT join custom1 on DlySale.btypeid = custom1.typeId LEFT join custom2 on DlySale.btypeid = custom2.typeId LEFT join Department on DlySale.btypeid = Department.typeId LEFT join Mtype on DlySale.btypeid = Mtype.typeId where Draft != 1").Tables[0];
                result.count = 0;
                result.data = dt1;
                result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
                return Newtonsoft.Json.JsonConvert.SerializeObject(result);
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select  DlySale.Vchcode,Btype.FullName as BtypeName,Employee.FullName as EmployeeName,Stock.FullName as StockName,ptype.FullName as ptypeName,DlySale.Qty,DlySale.discount,DlySale.DiscountPrice,DlySale.costtotal,DlySale.costprice,DlySale.Blockno,DlySale.price,DlySale.total,DlySale.Prodate,DlySale.TaxPrice,DlySale.TaxTotal,DlySale.comment,DlySale.date,DlySale.usedtype,DlySale.period,DlySale.tax_total,DlySale.tax,DlySale.discounttotal,T_GBL_Vchtype.fullname as T_GBL_VchtypeName,DlySale.redword,DlySale.dlyorder,DlySale.wldzgroup,DlySale.unit,DlySale.PDETAIL,DlySale.groupover,DlySale.SourceDlyOrder,DlySale.planprice,DlySale.plantotal,DlySale.Toqty,DlySale.RedOld,DlySale.recalc,workshop.FullName as workshopName, custom1.FullName as custom1Name, custom2.FullName as custom2Name, DlySale.FreeDom01, DlySale.FreeDom02, DlySale.FreeDom03, DlySale.FreeDom04, DlySale.FreeDom05, DlySale.WLDZ, DlySale.FreeDom06, DlySale.FreeDom07, DlySale.FreeDom08, DlySale.FreeDom09, DlySale.FreeDom10, DlySale.QtyOther, DlySale.ToOutQty, DlySale.InNumber, DlySale.InVchCode, DlySale.InVchType, DlySale.InDlyorder, DlySale.SourceVchcode, DlySale.SourceVchtype, DlySale.RowNo, Department.FullName as DepartmentName, DlySale.Appoint, DlySale.Draft, DlySale.CalcCostOrder, DlySale.FreeDom11, DlySale.FreeDom12, DlySale.FreeDom13, DlySale.FreeDom14, DlySale.FreeDom15, DlySale.FreeDom16, DlySale.FreeDomDateDif, DlySale.Custom3, DlySale.Custom4, Btype.FullName as BtypeName, DlySale.BtypeOtherCode, DlySale.DetailSign, DlySale.ProduceDate, DlySale.PriceTraceSign, Mtype.FullName as MtypeName from DlySale LEFT join Btype on DlySale.btypeid = Btype.typeId LEFT join Employee on DlySale.btypeid = Employee.typeId LEFT join Stock on DlySale.btypeid = Stock.typeId LEFT join ptype on DlySale.btypeid = ptype.typeId LEFT join T_GBL_Vchtype on DlySale.btypeid = T_GBL_Vchtype.typeId LEFT join workshop on DlySale.btypeid = workshop.typeId LEFT join custom1 on DlySale.btypeid = custom1.typeId LEFT join custom2 on DlySale.btypeid = custom2.typeId LEFT join Department on DlySale.btypeid = Department.typeId LEFT join Mtype on DlySale.btypeid = Mtype.typeId where Draft != 1 and Btype.FullName ='" + Search + "'").Tables[0];
            result.count = 0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        /// <summary>
        /// 进销存单据索引查询
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="Search"></param>
        /// <returns></returns>
        [AllowAnonymous]
        public string DlyndxList(int PageIndex = 1, int PageSize = 5, string Search = "")
        {
            GridResult result = new GridResult();
            if (Search == "")
            {
                DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query("select Dlyndx.Vchcode,Dlyndx.DATE,Dlyndx.NUMBER,Dlyndx.VchType,Dlyndx.summary,Btype.FullName as BtypeName,Employee.FullName as EmployeeName,Stock.FullName as StockName,Stock.FullName as StockName2,atypecw.FullName as atypecwName,Dlyndx.period,Dlyndx.RedWord,Dlyndx.RedOld,Employee.FullName as EmployeeName2,Dlyndx.draft,Dlyndx.Total,Dlyndx.GatheringDate,Department.FullName as DepartmentName,Employee.FullName as EmployeeName2,Dlyndx.SourceVchcode,Dlyndx.SourceVchType,Dlyndx.BillTotal,WorkShop.FullName as WorkShopName,Dlyndx.Compact,Dlyndx.UserDefined01,Dlyndx.UserDefined02,Dlyndx.UserDefined03,Dlyndx.MatchQty,Dlyndx.AuditLeveal,Btype.FullName as BtypeName2,Department.FullName as DepartmentName2,Dlyndx.Pubufts,Dlyndx.SaveTime,Dlyndx.DifAType,Dlyndx.Appoint,Dlyndx.Acc,Dlyndx.NoteCode,Dlyndx.isPreARAP,Dlyndx.InvoiceType,Dlyndx.CancelType,Btype.FullName as BtypeName3,Dlyndx.NormCost,Dlyndx.UserDefined04,Dlyndx.UserDefined05,Dlyndx.UserDefined06,Dlyndx.UserDefined07,Dlyndx.UserDefined08,Dlyndx.UserDefined09,Dlyndx.UserDefined10,Dlyndx.UserDefined11,Dlyndx.UserDefined12,Dlyndx.UserDefined13,Dlyndx.UserDefined14,Dlyndx.UserDefined15,	Dlyndx.UserDefined16,Dlyndx.SettleBtypeId,Dlyndx.IsCheckOut,Dlyndx.ModifyPerson,Dlyndx.ModifyDate,Dlyndx.LastYear,Dlyndx.BuildType,Dlyndx.BuildNo,Dlyndx.IsAutoBuild,Dlyndx.InstockBackSign,Dlyndx.AuditDate,Mtype.FullName as MtypeName,Dlyndx.BillType,Dlyndx.IsRejected from Dlyndx LEFT join Btype on Dlyndx.btypeid = Btype.typeId LEFT join Employee on Dlyndx.btypeid = Employee.typeId LEFT join Stock on Dlyndx.btypeid = Stock.typeId LEFT join ptype on Dlyndx.btypeid = ptype.typeId LEFT join T_GBL_Vchtype on Dlyndx.btypeid = T_GBL_Vchtype.typeId LEFT join workshop on Dlyndx.btypeid = workshop.typeId LEFT join custom1 on Dlyndx.btypeid = custom1.typeId LEFT join custom2 on Dlyndx.btypeid = custom2.typeId LEFT join Department on Dlyndx.btypeid = Department.typeId LEFT join Mtype on Dlyndx.btypeid = Mtype.typeId LEFT join  atypecw on Dlyndx.btypeid = atypecw.typeId  where Draft!=1").Tables[0];
                result.count = 0;
                result.data = dt1;
                result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
                return Newtonsoft.Json.JsonConvert.SerializeObject(result);
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select Dlyndx.Vchcode,Dlyndx.DATE,Dlyndx.NUMBER,Dlyndx.VchType,Dlyndx.summary,Btype.FullName as BtypeName,Employee.FullName as EmployeeName,Stock.FullName as StockName,Stock.FullName as StockName2,atypecw.FullName as atypecwName,Dlyndx.period,Dlyndx.RedWord,Dlyndx.RedOld,Employee.FullName as EmployeeName2,Dlyndx.draft,Dlyndx.Total,Dlyndx.GatheringDate,Department.FullName as DepartmentName,Employee.FullName as EmployeeName2,Dlyndx.SourceVchcode,Dlyndx.SourceVchType,Dlyndx.BillTotal,WorkShop.FullName as WorkShopName,Dlyndx.Compact,Dlyndx.UserDefined01,Dlyndx.UserDefined02,Dlyndx.UserDefined03,Dlyndx.MatchQty,Dlyndx.AuditLeveal,Btype.FullName as BtypeName2,Department.FullName as DepartmentName2,Dlyndx.Pubufts,Dlyndx.SaveTime,Dlyndx.DifAType,Dlyndx.Appoint,Dlyndx.Acc,Dlyndx.NoteCode,Dlyndx.isPreARAP,Dlyndx.InvoiceType,Dlyndx.CancelType,Btype.FullName as BtypeName3,Dlyndx.NormCost,Dlyndx.UserDefined04,Dlyndx.UserDefined05,Dlyndx.UserDefined06,Dlyndx.UserDefined07,Dlyndx.UserDefined08,Dlyndx.UserDefined09,Dlyndx.UserDefined10,Dlyndx.UserDefined11,Dlyndx.UserDefined12,Dlyndx.UserDefined13,Dlyndx.UserDefined14,Dlyndx.UserDefined15,	Dlyndx.UserDefined16,Dlyndx.SettleBtypeId,Dlyndx.IsCheckOut,Dlyndx.ModifyPerson,Dlyndx.ModifyDate,Dlyndx.LastYear,Dlyndx.BuildType,Dlyndx.BuildNo,Dlyndx.IsAutoBuild,Dlyndx.InstockBackSign,Dlyndx.AuditDate,Mtype.FullName as MtypeName,Dlyndx.BillType,Dlyndx.IsRejected from Dlyndx LEFT join Btype on Dlyndx.btypeid = Btype.typeId LEFT join Employee on Dlyndx.btypeid = Employee.typeId LEFT join Stock on Dlyndx.btypeid = Stock.typeId LEFT join ptype on Dlyndx.btypeid = ptype.typeId LEFT join T_GBL_Vchtype on Dlyndx.btypeid = T_GBL_Vchtype.typeId LEFT join workshop on Dlyndx.btypeid = workshop.typeId LEFT join custom1 on Dlyndx.btypeid = custom1.typeId LEFT join custom2 on Dlyndx.btypeid = custom2.typeId LEFT join Department on Dlyndx.btypeid = Department.typeId LEFT join Mtype on Dlyndx.btypeid = Mtype.typeId LEFT join  atypecw on Dlyndx.btypeid = atypecw.typeId  where Draft!=1 and Btype.FullName ='" + Search + "'").Tables[0];
            result.count = 0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        /// <summary>
        /// 费用支出明细表
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="Search"></param>
        /// <returns></returns>
        [AllowAnonymous]
        public string DlyAList(int PageIndex = 1, int PageSize = 5, string Search = "")
        {
            GridResult result = new GridResult();
            if (Search == "")
            {
                DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query("select DlyA.vchcode,AtypeCW.FullName as AtypeCWName,Btype.FullName as BtypeName,Employee.FullName as EmployeeName,Stock.FullName as StockName,DlyA.debittotal,DlyA.date,DlyA.period,DlyA.vchtype,DlyA.redword,DlyA.usedtype,DlyA.comment,Department.FullName as DepartmentName,DlyA.readonly,DlyA.lendtotal,DlyA.dlyorder,DlyA.type,DlyA.wldzgroup,DlyA.memo,DlyA.detailgroup,DlyA.total,DlyA.debitqty,DlyA.lendqty,Employee.FullName as EmployeeName2,Mtype.FullName as MtypeName,DlyA.GroupId,DlyA.UserDefined01,DlyA.UserDefined02,DlyA.ToTotal,DlyA.SourcedlyOrder,DlyA.WLDZ,DlyA.FreeDom01,DlyA.FreeDom02,DlyA.FreeDom03,DlyA.FreeDom04,DlyA.FreeDom05,DlyA.FreeDom06,DlyA.FreeDom07,DlyA.FreeDom08,DlyA.FreeDom09,DlyA.FreeDom10,DlyA.RowNo,DlyA.Draft,DlyA.DifAType,DlyA.FreeDom11,DlyA.FreeDom12,DlyA.FreeDom13,DlyA.FreeDom14,DlyA.FreeDom15,DlyA.FreeDom16,DlyA.FreeDomDateDif,DlyA.SourceVchCode,DlyA.SourceVchType,DlyA.iniToTotal,DlyA.WDebitTotal,DlyA.WLendTotal,DlyA.WRate,DlyA.WDebitTotalOriginal,DlyA.WLendTotalOriginal,DlyA.BusinessForeignId,DlyA.ForeignId,DlyA.ParVchtype from DlyA LEFT join AtypeCW on DlyA.btypeid=AtypeCW.typeId LEFT join Btype on DlyA.btypeid = Btype.typeId LEFT join Employee on DlyA.btypeid=Employee.typeId LEFT join Stock on DlyA.btypeid = Stock.typeId LEFT join Department on DlyA.btypeid = Department.typeId LEFT join Mtype on DlyA.btypeid = Mtype.typeId where Draft!=1").Tables[0];
                result.count = 0;
                result.data = dt1;
                result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
                return Newtonsoft.Json.JsonConvert.SerializeObject(result);
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select DlyA.vchcode,AtypeCW.FullName as AtypeCWName,Btype.FullName as BtypeName,Employee.FullName as EmployeeName,Stock.FullName as StockName,DlyA.debittotal,DlyA.date,DlyA.period,DlyA.vchtype,DlyA.redword,DlyA.usedtype,DlyA.comment,Department.FullName as DepartmentName,DlyA.readonly,DlyA.lendtotal,DlyA.dlyorder,DlyA.type,DlyA.wldzgroup,DlyA.memo,DlyA.detailgroup,DlyA.total,DlyA.debitqty,DlyA.lendqty,Employee.FullName as EmployeeName2,Mtype.FullName as MtypeName,DlyA.GroupId,DlyA.UserDefined01,DlyA.UserDefined02,DlyA.ToTotal,DlyA.SourcedlyOrder,DlyA.WLDZ,DlyA.FreeDom01,DlyA.FreeDom02,DlyA.FreeDom03,DlyA.FreeDom04,DlyA.FreeDom05,DlyA.FreeDom06,DlyA.FreeDom07,DlyA.FreeDom08,DlyA.FreeDom09,DlyA.FreeDom10,DlyA.RowNo,DlyA.Draft,DlyA.DifAType,DlyA.FreeDom11,DlyA.FreeDom12,DlyA.FreeDom13,DlyA.FreeDom14,DlyA.FreeDom15,DlyA.FreeDom16,DlyA.FreeDomDateDif,DlyA.SourceVchCode,DlyA.SourceVchType,DlyA.iniToTotal,DlyA.WDebitTotal,DlyA.WLendTotal,DlyA.WRate,DlyA.WDebitTotalOriginal,DlyA.WLendTotalOriginal,DlyA.BusinessForeignId,DlyA.ForeignId,DlyA.ParVchtype from DlyA LEFT join AtypeCW on DlyA.btypeid=AtypeCW.typeId LEFT join Btype on DlyA.btypeid = Btype.typeId LEFT join Employee on DlyA.btypeid=Employee.typeId LEFT join Stock on DlyA.btypeid = Stock.typeId LEFT join Department on DlyA.btypeid = Department.typeId LEFT join Mtype on DlyA.btypeid = Mtype.typeId where Draft!=1 and AtypeCW.FullName ='" + Search + "'").Tables[0];
            result.count = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.Query("select COUNT(*) as [count] from Test ").Tables[0]);
            result.data = dt;
            result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult DeleteUsersAsync(int ID)
        {
            var cookies = Web.Common.CookiesManagement.GetCookies();
            var ticket = Web.Common.CookiesManagement.GetTicket(cookies);
            GridResult result = new GridResult();
            FreeADO.DBUtility.DbHelperSQL.ExecuteSql("Delete Test where ID=" + ID + "");
            return Json(result);
        }
        [AllowAnonymous]
        public string VIPList(int PageIndex = 1, int PageSize = 20, string Search = "")
        {
            GridResult result = new GridResult();
            //SqlParameter[] parameters =
            //{
            //                                new SqlParameter("@PageIndex",SqlDbType.Int),
            //                                new SqlParameter("@PageSize",SqlDbType.Int),
            //                                new SqlParameter("@Search",SqlDbType.NVarChar,40000)};
            //parameters[0].Value = PageIndex;
            //parameters[1].Value = PageSize;
            //parameters[2].Value = Search;
            //DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_VIPList", parameters, "aaa", 3000);
            if (Search == "")
            {
                DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query("select * from nVipCardSign").Tables[0];
                result.count = 0;
                result.data = dt1;
                result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
                return Newtonsoft.Json.JsonConvert.SerializeObject(result);
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from nVipCardSign where Bname like '%"+ Search + "%'").Tables[0];
            result.count = 0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>(); //Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public string CodeList()
        {
            GridResult result = new GridResult();
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select VipCardCode from nVipCardSign").Tables[0];
            result.count = 0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>();
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
    } 
}